package com.bobo.dao.topic;

import com.bobo.dao.BaseDao;
import com.bobo.dto.VCTopic;
import com.bobo.model.Category;
import com.bobo.model.Photo;
import com.bobo.model.Topic;
import com.bobo.model.User;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by bobo on 16-10-28.
 */
public class TopicDaoImpl extends BaseDao<Topic> implements ITopicDao {
    @Override
    public Integer create(Topic topic) {
        String sql = "INSERT INTO t_topic(title, create_time, update_time, `describe`,content, photo_id, category_id, user_id)" +
                "VALUES (?,now(),now(),?,?,?,?,?)";
        return create(sql, topic.getTitle(), topic.getDescribe(), topic.getContent(), topic.getPhoto().getId(),
                topic.getCategory().getId(), topic.getUser().getId());
    }

    @Override
    public boolean update(Topic topic) {
        String sql = "UPDATE t_topic SET title=?,update_time=now(), status=?, `describe`=?,content=?, hits=?, photo_id=?," +
                "  category_id=?, top=?,review=? WHERE id=?";
        return update(sql, topic.getTitle(), topic.getStatus(), topic.getDescribe(), topic.getContent(), topic.getHits(),
                topic.getPhoto().getId(), topic.getCategory().getId(), topic.getTop(),
                topic.getReview(), topic.getId());
    }

    @Override
    public Topic getById(Integer id) {
        String sql = "SELECT *from v_topic WHERE topic_id=?";
        Topic topic = new Topic();
        ResultSet rs = getObject(sql, id);
        try {
            while (rs.next()) {
                topic.setId(rs.getInt("topic_id"));
                topic.setTitle(rs.getString("title"));
                topic.setCreateTime(rs.getDate("create_time"));
                topic.setUpdateTime(rs.getDate("update_time"));
                topic.setDescribe(rs.getString("describe"));
                topic.setContent(rs.getString("content"));
                topic.setStatus(rs.getInt("status"));
                topic.setTop(rs.getInt("top"));
                topic.setReview(rs.getString("review"));
                topic.setHits(rs.getInt("hits"));

                User user = new User();
                user.setId(rs.getInt("user_id"));
                user.setName(rs.getString("user_name"));
                topic.setUser(user);

                Category category = new Category();
                category.setId(rs.getInt("category_id"));
                category.setName(rs.getString("category_name"));
                topic.setCategory(category);

                Photo photo = new Photo();
                photo.setId(rs.getInt("photo_id"));
                photo.setImg(rs.getString("img"));
                topic.setPhoto(photo);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return topic;
    }

    @Override
    public List<Topic> getTopics(Integer userId, Integer categoryId, Integer current, Integer pageSize, Integer status) {
        StringBuilder sql = new StringBuilder("SELECT *from v_topic where 1=1");
        List<Topic> topics = new ArrayList<>();
        ResultSet rs;

        sql.append(" and status=?");

        Integer arg = null;

        if (userId != null) {
            sql.append(" and user_id=?");
            arg = userId;
        }
        if (categoryId != null) {
            sql.append(" and category_id=?");
            arg = categoryId;
        }

        status = status == null ? 1: status;
        sql.append(" ORDER BY top DESC ,create_time,hits DESC ");

        if(arg == null) {
            rs = pager(sql.toString(), current, pageSize,status );
        } else {
            rs = pager(sql.toString(), current, pageSize,status , arg);
        }

        try {
            while (rs.next()) {
                Topic topic = new Topic();
                topic.setId(rs.getInt("topic_id"));
                topic.setTitle(rs.getString("title"));
                topic.setCreateTime(rs.getDate("create_time"));
                topic.setUpdateTime(rs.getDate("update_time"));
                topic.setDescribe(rs.getString("describe"));
                topic.setContent(rs.getString("content"));
                topic.setStatus(rs.getInt("status"));
                topic.setTop(rs.getInt("top"));
                topic.setReview(rs.getString("review"));
                topic.setHits(rs.getInt("hits"));

                User user = new User();
                user.setId(rs.getInt("user_id"));
                user.setName(rs.getString("user_name"));
                topic.setUser(user);

                Category category = new Category();
                category.setId(rs.getInt("category_id"));
                category.setName(rs.getString("category_name"));
                topic.setCategory(category);

                Photo photo = new Photo();
                photo.setId(rs.getInt("photo_id"));
                photo.setImg(rs.getString("img"));
                topic.setPhoto(photo);

                topics.add(topic);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return topics;
    }

    @Override
    public List<VCTopic> getTus(Integer userId, Integer current, Integer pageSize) {
        String sql = "SELECT *FROM v_topic_count WHERE user_id=?";
        List<VCTopic> vcTopics = new ArrayList<>();
        ResultSet rs = pager(sql, current, pageSize, userId);
        try {
            while(rs.next()){
                VCTopic vct = new VCTopic();

                vct.setId(rs.getInt("id"));
                vct.setTitle(rs.getString("title"));
                vct.setCategory(rs.getString("category"));
                vct.setStatus(rs.getInt("status"));
                vct.setCreateTime(rs.getTimestamp("create_time"));
                vct.setUpdateTime(rs.getTimestamp("update_time"));
                vct.setFavourites(rs.getInt("favourites"));
                vct.setMessages(rs.getInt("messages"));
                vct.setImg(rs.getString("img"));
                vct.setHits(rs.getInt("hits"));
                vct.setUsername(rs.getString("username"));

                vcTopics.add(vct);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return vcTopics;
    }

    @Override
    public List<Topic> getTops(Integer top) {
        String sql = "SELECT *FROM v_topic WHERE top=?";
        List<Topic> topics = new ArrayList<>();
        ResultSet rs = getObject(sql, top);
        try {
            while (rs.next()) {
                Topic topic = new Topic();
                topic.setId(rs.getInt("topic_id"));
                topic.setTitle(rs.getString("title"));
                topic.setCreateTime(rs.getDate("create_time"));
                topic.setUpdateTime(rs.getDate("update_time"));
                topic.setDescribe(rs.getString("describe"));
                topic.setContent(rs.getString("content"));
                topic.setStatus(rs.getInt("status"));
                topic.setTop(rs.getInt("top"));
                topic.setReview(rs.getString("review"));
                topic.setHits(rs.getInt("hits"));


                User user = new User();
                user.setId(rs.getInt("user_id"));
                user.setName(rs.getString("user_name"));
                topic.setUser(user);

                Category category = new Category();
                category.setId(rs.getInt("category_id"));
                category.setName(rs.getString("category_name"));
                topic.setCategory(category);

                Photo photo = new Photo();
                photo.setId(rs.getInt("photo_id"));
                photo.setImg(rs.getString("img"));
                topic.setPhoto(photo);

                topics.add(topic);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return topics;
    }

    @Override
    public List<Topic> getLike(Integer current, Integer pageSize, String like) {
        String sql = "SELECT * from v_topic where 1=1  and status=1 ";
        sql = sql+"and title like '%"+like+"%' ORDER BY top DESC ,create_time,hits DESC ";
        ResultSet rs = getObject(sql);
        List<Topic> topics = new ArrayList<>();
        try {
            while (rs.next()) {
                Topic topic = new Topic();
                topic.setId(rs.getInt("topic_id"));
                topic.setTitle(rs.getString("title"));
                topic.setCreateTime(rs.getDate("create_time"));
                topic.setUpdateTime(rs.getDate("update_time"));
                topic.setDescribe(rs.getString("describe"));
                topic.setContent(rs.getString("content"));
                topic.setStatus(rs.getInt("status"));
                topic.setTop(rs.getInt("top"));
                topic.setReview(rs.getString("review"));
                topic.setHits(rs.getInt("hits"));

                User user = new User();
                user.setId(rs.getInt("user_id"));
                user.setName(rs.getString("user_name"));
                topic.setUser(user);

                Category category = new Category();
                category.setId(rs.getInt("category_id"));
                category.setName(rs.getString("category_name"));
                topic.setCategory(category);

                Photo photo = new Photo();
                photo.setId(rs.getInt("photo_id"));
                photo.setImg(rs.getString("img"));
                topic.setPhoto(photo);

                topics.add(topic);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return topics;
    }

    @Override
    public Integer getCount(Integer status, Integer userId, Integer categoryId) {
        StringBuilder sql = new StringBuilder("SELECT count(*) from v_topic where status=?");
        Integer pageNum = 0;
        ResultSet rs;
        if (userId != null) {
            sql.append(" and user_id=?");
            rs = getObject(sql.toString(), status, userId);
        } else if (categoryId != null) {
            sql.append(" and category_id=?");
            rs = getObject(sql.toString(), status, categoryId);
        } else {
            rs = getObject(sql.toString(), status);
        }


        try {
            while (rs.next()) pageNum = rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pageNum;
    }

    @Override
    public List<VCTopic> getVCTopics( Integer current, Integer pageSize, String sortName, String sort, String like) {
        StringBuilder sql = new StringBuilder("SELECT * from v_topic_count where 1=1 ");
        if (like != null) {
            sql.append(" and ");
            sql.append(" title like '%");
            sql.append(like.trim());
            sql.append("%' or ");
            sql.append(" username like '%");
            sql.append(like.trim());
            sql.append("%' ");
        }




        sql.append(" order by ");
        if (sortName != null) {
            sql.append(sortName);
        } else {
            sql.append(" id ");
        }

        if (sort != null) {
            sql.append(" ");
            sql.append(sort);
        }

        sql.append(" limit ");
        sql.append((current - 1) * pageSize);
        sql.append(",");
        sql.append(pageSize);


        ResultSet rs = getObject(sql.toString());
        List<VCTopic> vcTopics = new ArrayList<>();

        try {
            while (rs.next()) {
                VCTopic vct = new VCTopic();

                vct.setId(rs.getInt("id"));
                vct.setTitle(rs.getString("title"));
                vct.setCategory(rs.getString("category"));
                vct.setStatus(rs.getInt("status"));
                vct.setCreateTime(rs.getTimestamp("create_time"));
                vct.setUpdateTime(rs.getTimestamp("update_time"));
                vct.setFavourites(rs.getInt("favourites"));
                vct.setMessages(rs.getInt("messages"));
                vct.setImg(rs.getString("img"));
                vct.setHits(rs.getInt("hits"));
                vct.setUsername(rs.getString("username"));

                vcTopics.add(vct);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }



        return vcTopics;
    }

    @Override
    public Integer getVCTCount(String like) {

        StringBuilder sql = new StringBuilder("SELECT count(*) from v_topic_count where 1=1 ");
        Integer pageNum = 0;
        if (like != null) {
            sql.append(" and ");
            sql.append(" title like '%");
            sql.append(like.trim());
            sql.append("%' or ");
            sql.append(" username like '%");
            sql.append(like.trim());
            sql.append("%' ");
        }

        ResultSet rs = getObject(sql.toString());
        try {
            while (rs.next()) pageNum = rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return pageNum;
    }
}
